import mysql from 'mysql2/promise';
import dotenv from 'dotenv';
import { Connection, PoolOptions, Pool } from 'mysql2/promise';

// 加载环境变量
dotenv.config({ path: '.env.local' });

// 数据库配置
const dbConfig: PoolOptions = {
  host: process.env.DB_HOST || 'rm-bp14wd51i709c3z79ho.mysql.rds.aliyuncs.com',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'Ircnjfdhnvxwz@123',
  database: process.env.DB_NAME || 'xiaoyoubao',
  port: Number(process.env.DB_PORT) || 3306,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 10000, // 10秒
  charset: 'utf8mb4',
  decimalNumbers: true, // 返回数字而不是字符串
  dateStrings: true, // 返回日期字符串而不是日期对象
};

// 连接池实例
let pool: Pool | null = null;

// 获取或创建连接池，带重试机制
const getPool = (retry = 0, maxRetries = 3): Pool => {
  if (!pool) {
    console.log(`创建新的数据库连接池...${retry > 0 ? `(重试 ${retry}/${maxRetries})` : ''}`);
    try {
      pool = mysql.createPool({
        ...dbConfig,
        connectTimeout: 10000, // 10秒连接超时
      });
      console.log('数据库连接池创建成功');
    } catch (err) {
      console.error('数据库连接池创建失败:', err);
      
      // 尝试重试
      if (retry < maxRetries) {
        console.log(`将在 ${retry + 1} 秒后重试连接...`);
        // 在同步代码中，我们不能使用异步重试，所以这里记录错误并继续抛出
      }
      
      throw err;
    }
  }
  return pool;
};

// 测试连接
const testConnection = async (retry = 0, maxRetries = 3): Promise<void> => {
  try {
    const pool = getPool();
    const connection = await pool.getConnection();
    console.log('数据库连接成功 - 阿里云RDS实例');
    console.log(`连接到: ${dbConfig.host}:${dbConfig.port}`);
    console.log(`数据库: ${dbConfig.database}`);
    connection.release();
  } catch (err) {
    console.error(`数据库连接测试失败 (尝试 ${retry + 1}/${maxRetries + 1}):`, err);
    
    // 尝试重试
    if (retry < maxRetries) {
      const retryDelay = (retry + 1) * 1000; // 逐步增加重试延迟
      console.log(`将在 ${retryDelay / 1000} 秒后重试连接...`);
      
      // 使用setTimeout进行异步重试
      setTimeout(() => {
        testConnection(retry + 1, maxRetries)
          .catch(e => console.error('重试连接失败:', e));
      }, retryDelay);
      return;
    }
    
    throw err;
  }
};

// 执行SQL查询，带重试机制
export async function query<T>(sql: string, params?: any[], retryCount = 0): Promise<T> {
  const maxRetries = 2; // 最多重试2次
  
  try {
    console.log('执行SQL:', sql);
    console.log('SQL参数:', params ? JSON.stringify(params) : '无');
    
    try {
      // 确保连接池存在
      const pool = getPool();
      
      // 增加超时检测
      const queryPromise = pool.execute(sql, params);
      
      // 设置超时
      const timeoutPromise = new Promise((_, reject) => {
        setTimeout(() => reject(new Error('数据库查询超时(10秒)')), 10000);
      });
      
      // 使用Promise.race竞争
      const [results] = await Promise.race([queryPromise, timeoutPromise]) as any;
      
      console.log('SQL执行成功，返回记录数:', Array.isArray(results) ? results.length : '1条(非数组)');
      return results as unknown as T;
    } catch (dbError: any) {
      // 记录详细错误信息
      console.error('数据库错误:', dbError.message);
      if (dbError.code) {
        console.error('错误代码:', dbError.code);
        console.error('SQL状态:', dbError.sqlState);
        console.error('错误编号:', dbError.errno);
      }
      
      // 检查是否是可以重试的错误
      const retryableErrors = ['ETIMEDOUT', 'ECONNREFUSED', 'PROTOCOL_CONNECTION_LOST', 'PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR'];
      const shouldRetry = retryableErrors.includes(dbError.code) && retryCount < maxRetries;
      
      if (shouldRetry) {
        console.log(`遇到可重试错误(${dbError.code})，等待重试...`);
        
        // 计算退避时间
        const retryDelay = Math.min(1000 * Math.pow(2, retryCount), 10000); // 最长10秒
        console.log(`将在 ${retryDelay / 1000} 秒后尝试重新执行查询(${retryCount + 1}/${maxRetries})...`);
        
        // 等待一段时间后重试
        await new Promise(resolve => setTimeout(resolve, retryDelay));
        
        // 重置连接池，强制创建新连接
        pool = null;
        
        // 递归重试
        return query<T>(sql, params, retryCount + 1);
      }
      
      // 重新抛出错误以便上层捕获
      throw dbError;
    }
  } catch (error) {
    console.error('SQL执行错误:', error);
    throw error;
  }
}

// 获取单条记录
export async function findUnique<T>(
  table: string, 
  whereClause: Record<string, any>
): Promise<T | null> {
  try {
    const whereKeys = Object.keys(whereClause);
    const whereConditions = whereKeys.map(key => `${key} = ?`).join(' AND ');
    const whereValues = whereKeys.map(key => whereClause[key]);
    
    const sql = `SELECT * FROM ${table} WHERE ${whereConditions} LIMIT 1`;
    
    const results = await query<any[]>(sql, whereValues);
    return results.length > 0 ? results[0] as T : null;
  } catch (error) {
    console.error(`findUnique错误 (表 ${table}):`, error);
    throw error;
  }
}

// 创建记录
export async function create<T>(
  table: string, 
  data: Record<string, any>
): Promise<T> {
  try {
    const keys = Object.keys(data);
    const values = Object.values(data);
    const placeholders = keys.map(() => '?').join(', ');
    
    const sql = `INSERT INTO ${table} (${keys.join(', ')}) VALUES (${placeholders})`;
    
    const result = await query<mysql.ResultSetHeader>(sql, values);
    
    // 如果需要返回完整插入的记录
    if (result.insertId) {
      const insertedRecord = await findUnique<T>(table, { id: result.insertId });
      if (insertedRecord) {
        return insertedRecord;
      }
    }
    
    // 返回插入ID
    return { id: result.insertId } as unknown as T;
  } catch (error) {
    console.error(`create错误 (表 ${table}):`, error);
    throw error;
  }
}

// 更新记录
export async function update<T>(
  table: string,
  id: number,
  data: Record<string, any>
): Promise<T | null> {
  try {
    const keys = Object.keys(data);
    const setClause = keys.map(key => `${key} = ?`).join(', ');
    const values = [...Object.values(data), id];
    
    const sql = `UPDATE ${table} SET ${setClause} WHERE id = ?`;
    
    await query<mysql.ResultSetHeader>(sql, values);
    
    // 返回更新后的记录
    return findUnique<T>(table, { id });
  } catch (error) {
    console.error(`update错误 (表 ${table}):`, error);
    throw error;
  }
}

// 删除记录
export async function remove(
  table: string, 
  whereClause: Record<string, any>
): Promise<boolean> {
  try {
    const whereKeys = Object.keys(whereClause);
    const whereConditions = whereKeys.map(key => `${key} = ?`).join(' AND ');
    const whereValues = whereKeys.map(key => whereClause[key]);
    
    const sql = `DELETE FROM ${table} WHERE ${whereConditions}`;
    
    const result = await query<mysql.ResultSetHeader>(sql, whereValues);
    
    return result.affectedRows > 0;
  } catch (error) {
    console.error(`remove错误 (表 ${table}):`, error);
    throw error;
  }
}

// 查找多条记录
export async function findMany<T>(
  table: string,
  options: {
    where?: Record<string, any>;
    orderBy?: Record<string, 'asc' | 'desc'>;
    limit?: number;
    offset?: number;
  } = {}
): Promise<T[]> {
  try {
    let sql = `SELECT * FROM ${table}`;
    const params: any[] = [];
    
    // 处理 WHERE 条件
    if (options.where && Object.keys(options.where).length > 0) {
      const whereKeys = Object.keys(options.where);
      const whereConditions = whereKeys.map(key => `${key} = ?`).join(' AND ');
      const whereValues = whereKeys.map(key => options.where![key]);
      
      sql += ` WHERE ${whereConditions}`;
      params.push(...whereValues);
    }
    
    // 处理 ORDER BY
    if (options.orderBy && Object.keys(options.orderBy).length > 0) {
      const orderByEntries = Object.entries(options.orderBy);
      const orderByClause = orderByEntries.map(([key, direction]) => `${key} ${direction}`).join(', ');
      
      sql += ` ORDER BY ${orderByClause}`;
    }
    
    // 处理 LIMIT 和 OFFSET
    if (options.limit) {
      sql += ` LIMIT ?`;
      params.push(options.limit);
      
      if (options.offset) {
        sql += ` OFFSET ?`;
        params.push(options.offset);
      }
    }
    
    return query<T[]>(sql, params);
  } catch (error) {
    console.error(`findMany错误 (表 ${table}):`, error);
    throw error;
  }
}

// 事务支持
export async function transaction<T>(callback: (connection: Connection) => Promise<T>): Promise<T> {
  const pool = getPool();
  const connection = await pool.getConnection();
  
  try {
    await connection.beginTransaction();
    
    const result = await callback(connection);
    
    await connection.commit();
    return result;
  } catch (error) {
    await connection.rollback();
    console.error('事务执行错误:', error);
    throw error;
  } finally {
    connection.release();
  }
}

// 初始化时测试连接
testConnection().catch(err => {
  console.error('初始化数据库连接失败:', err);
});

export default getPool();